﻿using Common;
using Vision001.Models;
using MySqlConnector;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Vision001.Helper
{
    /// <summary>
    /// 数据库操作帮助类
    /// </summary>
    public static class SqlSugarHelper
    {
        /// <summary>
        /// 产能读取
        /// </summary>
        public static List<SearchData> SqlRead(string TableName, int ChoosedateIndex)
        {
            List<SearchData> data = new List<SearchData>();
            try
            {
                bool tableExists = Global._sqlSugarClient.DbMaintenance.IsAnyTable(TableName, false);

                if (tableExists)
                {
                    //如果表存在
                    //如果存在，查询
                    List<CapacityData> list = Global._sqlSugarClient.Queryable<CapacityData>().AS(TableName).ToList();
                    if (ChoosedateIndex == 0)
                    {
                        //白班
                        for (int i = 8; i < 20; i++)
                        {
                            SearchData searchData = new SearchData();
                            searchData.Time = list[i].Time;
                            searchData.Total = (list[i].OKnums + list[i].NGnums).ToString();
                            searchData.OKNum = list[i].OKnums.ToString();
                            searchData.NGnum = list[i].NGnums.ToString();
                            data.Add(searchData);
                        }
                    }
                    else if (ChoosedateIndex == 1)
                    {
                        //夜班
                        for (int i = 20; i < list.Count; i++)
                        {
                            SearchData searchData = new SearchData();
                            searchData.Time = list[i].Time;
                            searchData.Total = (list[i].OKnums + list[i].NGnums).ToString();
                            searchData.OKNum = list[i].OKnums.ToString();
                            searchData.NGnum = list[i].NGnums.ToString();
                            data.Add(searchData);
                        }

                        for (int i = 0; i < 8; i++)
                        {
                            SearchData searchData = new SearchData();
                            searchData.Time = list[i].Time;
                            searchData.Total = (list[i].OKnums + list[i].NGnums).ToString();
                            searchData.OKNum = list[i].OKnums.ToString();
                            searchData.NGnum = list[i].NGnums.ToString();
                            data.Add(searchData);
                        }
                    }
                    else
                    {
                        //白夜班合计
                        for (int i = 0; i < list.Count; i++)
                        {
                            SearchData searchData = new SearchData();
                            searchData.Time = list[i].Time;
                            searchData.Total = (list[i].OKnums + list[i].NGnums).ToString();
                            searchData.OKNum = list[i].OKnums.ToString();
                            searchData.NGnum = list[i].NGnums.ToString();
                            data.Add(searchData);
                        }
                    }
                    //返回一个总合计
                    SearchData AllData = new SearchData();
                    AllData.Total = data.Sum(s => int.Parse(s.Total)).ToString();
                    AllData.OKNum = data.Sum(s => int.Parse(s.OKNum)).ToString();
                    AllData.NGnum = data.Sum(s => int.Parse(s.NGnum)).ToString();
                    AllData.Time = "当前班次总计";
                    data.Add(AllData);
                }
                else
                {
                    //如果表不存在
                    //不存在则创建新表
                    Global._sqlSugarClient.CodeFirst.As<CapacityData>(TableName).InitTables<CapacityData>();
                    //初始化表的24h产能
                    Global._sqlSugarClient.Insertable(Global._CapacityDatas).AS(TableName).ExecuteCommand();
                    throw new Exception("数据表不存在，已经创建新表!");
                }
            }
            catch (Exception e)
            {
                throw e;
            }

            return data;
        }

        /// <summary>
        ///根据当前日期，实现产能写入
        /// </summary>
        public static void SqlWrite(int Num, bool isOk)
        {
            DateTime alltime = DateTime.Now;

            if (alltime.Hour < 8)
            {
                //八点之前，昨天的夜班
                alltime = alltime.AddDays(-1);
            }
            string TableName = alltime.ToString("yyyy-MM-dd") + "capacity";
            try
            {
                //先判断表存不存在
                ////表名固定格式
                bool tableExists = Global._sqlSugarClient.DbMaintenance.IsAnyTable(TableName);
                if (tableExists)
                {
                    if (isOk)
                    {
                        //ok数据

                        //转化当前时间
                        string time = alltime.Hour.ToString() + ":00";

                        //修改数据
                        Global._sqlSugarClient.Updateable<CapacityData>()
                            .SetColumns(u => new CapacityData()
                            {
                                OKnums = u.OKnums + Num
                            }).AS(TableName)
                            .Where(u => u.Time == time)
                            .ExecuteCommand();
                    }
                    else
                    {
                        //ng数据

                        //转化当前时间
                        string time = alltime.Hour.ToString() + ":00";

                        //修改数据
                        Global._sqlSugarClient.Updateable<CapacityData>()
                            .SetColumns(u => new CapacityData()
                            {
                                NGnums = u.NGnums + Num
                            }).AS(TableName)
                            .Where(u => u.Time == time)
                            .ExecuteCommand();
                    }
                }
                else
                {
                    //不存在则创建新表
                    Global._sqlSugarClient.CodeFirst.As<CapacityData>(TableName).InitTables<CapacityData>();
                    //初始化表的24h产能
                    Global._sqlSugarClient.Insertable(Global._CapacityDatas).AS(TableName).ExecuteCommand();
                    if (isOk)
                    {
                        //ok数据

                        //转化当前时间
                        string time = alltime.Hour.ToString() + ":00";

                        //修改数据
                        Global._sqlSugarClient.Updateable<CapacityData>()
                            .SetColumns(u => new CapacityData()
                            {
                                OKnums = u.OKnums + Num
                            }).AS(TableName)
                            .Where(u => u.Time == time)
                            .ExecuteCommand();
                    }
                    else
                    {
                        //ng数据

                        //转化当前时间
                        string time = alltime.Hour.ToString() + ":00";

                        //修改数据
                        Global._sqlSugarClient.Updateable<CapacityData>()
                            .SetColumns(u => new CapacityData()
                            {
                                NGnums = u.NGnums + Num
                            }).AS(TableName)
                            .Where(u => u.Time == time)
                            .ExecuteCommand();
                    }

                    throw new Exception("数据表不存在，已经创建新表!");
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}